import com.oracle.javafx.jmx.json.JSONReader;
import net.sf.json.JSONObject;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPFileFilter;

import java.io.*;
import java.nio.charset.Charset;
import java.sql.*;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.CountDownLatch;

/**
 * Created by 01sr on 2017/8/10.
 */
public class Dump {
    private static String configPath = "/Users/01sr/IdeaProjects/dumpsqlfromtxt/src/config.json";

    private static JSONObject object;
    //数据库所需信息
    private static String databaseAddr = null;
    private static String databaseName = null;
    private static String user = null;
    private static String passwd = null;

    //相关文件信息
    private static String fileMap = null;
    private static String ftpAddr = null;
    private static String ftpUser = null;
    private static String ftpPasswd =null;
    private static String dataPath = null;
    private static int lastUpdated = 0;

    public static void handleTxt(String path, HashMap<String, HashMap<String, String>> map) throws IOException, SQLException {
        Connection conn = null;
        BufferedReader br = null;
        FTPClient ftp = null;
        try {
            ftp = new FTPClient();
            ftp.connect(ftpAddr);
            ftp.login(ftpUser,ftpPasswd);

            FTPFile[] files = null;
            files = ftp.listFiles(path, new FTPFileFilter() {
                @Override
                public boolean accept(FTPFile ftpFile) {
//                    return Calendar.getInstance().getTimeInMillis()-ftpFile.getTimestamp().getTimeInMillis() <= 7*24*3600*1000&&ftpFile.getName().contains("-");
                    return ftpFile.getName().contains("-");
                }
            });
            if(files.length==0){
                System.out.println("无更新内容");
                return;
            }

            //根据日期获取最新数据 。。。
            Arrays.sort(files, new Comparator<FTPFile>() {
                @Override
                public int compare(FTPFile o1, FTPFile o2) {
                    if((Integer.valueOf(o1.getName().split("-")[1]))==(Integer.valueOf(o2.getName().split("-")[1])))
                        return 0;
                    if((Integer.valueOf(o1.getName().split("-")[1]))>(Integer.valueOf(o2.getName().split("-")[1])))
                        return -1;
                    else return 1;
                }
            });

            int t = Integer.parseInt(files[0].getName().split("-")[1].split("\\.")[0]);
            if(t <= lastUpdated){
                System.out.println("无更新内容");
                return;
            }

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection("jdbc:sqlserver://" + databaseAddr + ";DatabaseName=" + databaseName, user, passwd);

            for (int i = 0; i < files.length; i++) {
                String[] splitFile = files[i].getName().split("-");
                if(Integer.parseInt(splitFile[1].split("\\.")[0])!=t) break;
                String fileName = splitFile[0].toLowerCase();
                String tableName = map.get("table_name").get(fileName);
                if (tableName == null) {
                    System.out.println("Can't find table, skip " + fileName);
                    continue;
                }
                HashMap fmap = map.get(fileName);
                if (fmap == null) {
                    System.out.println("Can't get fields map, skip " + fileName);
                    continue;
                }
                System.out.println("正在更新"+tableName+"...");
                //删除表数据
                Statement st = conn.createStatement();
                st.executeUpdate("delete " + tableName);

                br = new BufferedReader(new InputStreamReader(ftp.retrieveFileStream(path + File.separator + files[i].getName()),Charset.forName("GBK")));
                int nums = 5000; //每次插入的行数

                StringBuilder sql = new StringBuilder(); //数据插入sql语句
                StringBuilder fieldsBuilder = new StringBuilder("("); //字段名拼接字符串
                String fields = null; //sql语句字段名部分
                String line = null;
                if ((line = br.readLine()) != null) {
                    String[] tmp = line.split("\\[,\\]");
                    for (int j = 0; j < tmp.length; j++) {
                        if (fmap.containsKey(tmp[j].trim().toLowerCase())) {
                            fieldsBuilder.append(fmap.get(tmp[j].trim().toLowerCase()) + ", ");
                        } else {
                            fieldsBuilder.append(",");
                        }
                    }
                    fieldsBuilder.delete(fieldsBuilder.length() - 2, fieldsBuilder.length());
                    fieldsBuilder.append(')');
                    fields = fieldsBuilder.toString();

                    int k = 0;

                    sql.append("insert into ")
                            .append(tableName)
                            .append(" ")
                            .append(fields)
                            .append(" values(");
                    for (int ii = 0; ii < tmp.length; ii++)
                        sql.append("?,");
                    sql.delete(sql.length() - 1, sql.length());
                    sql.append(")");
                    PreparedStatement stmt = conn.prepareStatement(sql.toString());
                    while ((line = readRow(br)) != null) {

                        String[] values = line.split("\\[,\\]");//正则字符串
                        values[values.length - 1] = values[values.length - 1].replace("[.]", "");//普通字符串
                        for (int ii = 0; ii < values.length; ii++) {
                            stmt.setString(ii + 1, values[ii]);
                        }
                        stmt.addBatch();
                        k++;
                        if (k == nums) {
                            k = 0;
                            //...插入数据
                            stmt.executeBatch();
                            conn.commit();
                        }
                    }
                    if (k != 0) {
                        stmt.executeBatch();
                        conn.commit();
                    }
                }
                if (br != null) br.close();
            }

            //更新standardTotal 的A200字段
            String sql = "update standardTotal set standardTotal.A200 = standardStats.id from standardStats where standardTotal.stat = standardStats.stats";
            Statement st = conn.createStatement();
            st.executeUpdate(sql);

            //更新最后更新时间
            updateConfig("lastUpdated",t);

        } catch (ClassNotFoundException e) {
            System.out.println("更新失败，错误信息：" + e.getMessage());
        } finally {
            if(ftp != null) ftp.disconnect();
            if(conn != null) conn.close();
        }
    }
    public static void main(String []args)  {
        HashMap map = null;
        try {
            initConfig();
            map = getFieldMap(fileMap);
            long startTime=System.currentTimeMillis();   //获取开始时间
            handleTxt(dataPath, map);
            long endTime=System.currentTimeMillis(); //获取结束时间
            System.out.println("程序运行时间： "+(endTime-startTime)+"ms");
        } catch (Exception e) {
            System.out.println("读取文件内容出错");
            e.printStackTrace();
        }
    }

    private static void initConfig() throws IOException {
        File file = new File(configPath);
        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
        String line = null;
        StringBuilder sb = new StringBuilder();
        while((line = br.readLine())!=null){
            sb.append(line);
        }
        String json = sb.toString();
        System.out.println(json);
        object =JSONObject.fromObject(json);

        databaseAddr = object.getString("databaseAddr");
        databaseName = object.getString("databaseName");
        user = object.getString("user");
        passwd = object.getString("passwd");

        fileMap = object.getString("fileMap");

        ftpAddr = object.getString("ftpAddr");
        ftpUser = object.getString("ftpUser");
        ftpPasswd = object.getString("ftpPasswd");
        dataPath = object.getString("dataPath");
        lastUpdated = object.getInt("lastUpdated");
        br.close();
    }

    private static <T> void updateConfig(String key, T value) throws IOException {
        object.put(key, value);
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(configPath))));
        bw.write(object.toString());
        bw.close();
    }

    //readRow读取对应数据库的一行数据，在文件txt中可能为多行
    public static String readRow(BufferedReader br) throws IOException {
        String line = null;
        StringBuilder sb = new StringBuilder();
        while((line=br.readLine())!=null){
            sb.append(line);
            if(line.endsWith("[.]")){
                return sb.toString();
            }
        }
        return null;
    }

    private static HashMap<String, HashMap<String, String>> getFieldMap(String path) throws IOException {
        HashMap<String, HashMap<String, String>> map = new HashMap<>();
        map.put("table_name", new HashMap<>());
        File file = new File(path);
        if (file.isFile() && file.exists()) { //判断文件是否存在
            InputStreamReader read = new InputStreamReader(
                    new FileInputStream(file));//考虑到编码格式
            BufferedReader bufferedReader = new BufferedReader(read);
            String lineTxt = null;
            String tableName = null;
            while ((lineTxt = bufferedReader.readLine()) != null) {
                if (!lineTxt.contains("-")) {
                    HashMap<String, String> fmap = new HashMap<>();
                    String[] names = lineTxt.trim().toLowerCase().split(",");
                    tableName = names[1];
                    map.put(tableName, fmap);
                    map.get("table_name").put(names[1], names[0]);
                    continue;
                }
                String[] fields = lineTxt.split("-");
                map.get(tableName).put(fields[1].trim().toLowerCase(), fields[0].trim());
            }
            read.close();
        }
        return map;
    }
}
